using System;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;

namespace Tino.Core.Data
{
    internal class SqliteAdapter
    {
        SQLiteConnection connection;

        internal SqliteAdapter(string connectionString)
        {
            if (connectionString == "")
                throw new ArgumentException("No connectionString provided.");

            connection = new SQLiteConnection(connectionString);
        }

        #region Static members

        internal static SqliteAdapter Get()
        {
            return new SqliteAdapter(Context.Current.ConnectionString);
        }

        #endregion

        void Open()
        {
            connection.Open();
        }

        void Close()
        {
            connection.Close();
        }

        void parseParameters(SQLiteCommand command, params object[] args)
        {
            if (args.Length % 2 != 0)
                throw new ArgumentException("args must be a pair number, such as @param1, value1, @param2, value2 etc...");

            for (int i = 0; i < args.Length; i = i + 2)
            {
                command.Parameters.Add(new SQLiteParameter((string)args[i], args[i + 1]));
            }
        }

        internal DataTable Execute(string Query, params object[] args)
        {
            SQLiteDataAdapter adapter = new SQLiteDataAdapter();
            SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = Query;
            
            if (args.Length > 0)
                parseParameters(command, args);

            adapter.SelectCommand = command;
            DataTable dt = new DataTable();
            // Open the connection
            Open();            
            try
            {
                Debug.Write(adapter.SelectCommand.CommandText);
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(string.Format("Error executing the SQL statement '{0}'", Query), ex);
            }
            finally
            {
                Close();
                adapter.Dispose();
                command.Dispose();
            }            
            return dt;
        }

        internal void ExecuteNonQuery(string Query, params object[] args)
        {
            // Open the connection
            Open();
            // Create the command
            SQLiteCommand command = new SQLiteCommand();
            // Init the transaction
            SQLiteTransaction transaction = connection.BeginTransaction();
            command.Connection = connection;
            command.Transaction = transaction;            
            try
            {
                // Assign the query to the command object
                command.CommandText = Query;
                if (args.Length > 0)
                    parseParameters(command, args);

                Debug.Write(command.CommandText);
                // Execute the query
                command.ExecuteNonQuery();
                // Commit
                transaction.Commit();
            }
            catch (Exception ex)
            {
                // Roll back so that no corrupt data is stored
                transaction.Rollback();
                throw new ApplicationException(string.Format("Error executing the SQL statement '{0}'", string.Format(Query, args)), ex);
            }
            finally
            {
                // Always close the connection
                Close();
                command.Dispose();
                transaction.Dispose();
            }
        }

        internal object ExecuteScalar(string Query, params object[] args)
        {
            DataTable dt = Execute(Query, args);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0];
            }
            else return null;
        }
    }
}
